package sc.test.spring.jdbc;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;

import sc.common.test.TestUtil;

public class JdbcUtil {

    public static String ORACLE_CLASS_NAME = "oracle.jdbc.OracleDriver";

    public static SqlRowSet query(String url, String username, String password, String sql) {
        BasicDataSource ds = createBasicDataSource(url, username, password, ORACLE_CLASS_NAME);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        return jdbcTemplate.queryForRowSet(sql);
    }

    public static PoolingDataSource createPoolDataSource(String url, String username, String password,
            String driverclass) {
        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(url, username, password);
        PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory,
                new GenericObjectPool(), null, null, false, true);
        ObjectPool connectionPool = new GenericObjectPool(poolableConnectionFactory);
        PoolingDataSource dataSource = new PoolingDataSource(connectionPool);
        return dataSource;
    }

    public static BasicDataSource createBasicDataSource(String url, String username, String password, String driverclass) {
        BasicDataSource ds = new BasicDataSource();
        ds.setUrl(url);
        ds.setUsername(username);
        ds.setPassword(password);
        ds.setDriverClassName(driverclass);
        ds.setMaxActive(50);
        ds.setMaxIdle(2);
        ds.setMinIdle(1);
        return ds;
    }

    public static void printMetaData(SqlRowSetMetaData md) {
        int colcount = md.getColumnCount();
        for (int i = 1; i <= colcount; ++i) {
            TestUtil.sp(
                    "(%2d) CatalogName[%s], ColumnClassName[%-32s], ColumnDisplaySize[%4d], ColumnLabel[%16s], "
                            + "ColumnName[%16s], ColumnType[%4d], ColumnTypeName[%16s], Precision[%3d], Scale[%2d], SchemaName[%16s], TableName[%16s]",
                    i, md.getCatalogName(i), md.getColumnClassName(i), md.getColumnDisplaySize(i),
                    md.getColumnLabel(i), md.getColumnName(i), md.getColumnType(i), md.getColumnTypeName(i),
                    md.getPrecision(i), md.getScale(i), md.getSchemaName(i), md.getTableName(i));
        }
    }

}
